﻿using OfficeOpenXml;
using System;
using System.Xml;
using OfficeOpenXml.Style;
using System.Linq;
using System.Drawing;
using System.Collections.Generic;
using System.Collections;
using System.IO;
using OfficeOpenXml.Drawing;
using knfUpdateDataLayer;

namespace LibreriaNuovaExcel
{
   


    public class generaReportExcelRevamped
    {
        #region Proprietà
        public ExcelPackage localPackage { get; set; }
        public string _filePath { get; set; }
        public string _reporter { get; set; }
        public string _aliasProduct { get; set; }
        public string _kindOfReport { get; set; }
        public string _PathImmagine { get; set; }
        public bool _isAveraged { get; set; }
        public List<int> _listaAnni { get; set; }
        public List<string> _listaPartnersImport { get; set; }
        public List<string> _listaPartnersExport { get; set; }
        #endregion

        #region Variabili Locali
        Color cMarrone = System.Drawing.ColorTranslator.FromHtml("#FF9900");
        Color cAzzurrino = System.Drawing.ColorTranslator.FromHtml("#D5E9EA");
        System.Drawing.Color cGrigetto = System.Drawing.Color.LightGray;
        knfEntities _db = new knfEntities();
        List<RawData> QueryInizialeReporter = new List<RawData>();
        List<RawData> QueryInizialePartner = new List<RawData>();
        #endregion

        public generaReportExcelRevamped(string filePath, string reporter, string aliasProduct, string kindOfReport,
            List<int> listaAnni, List<string>  listaPartnersImport, List<string>  listaPartnersExport, string PathImmagine, bool isAveraged)
        {
            _filePath = filePath;
            _reporter = reporter;
            _aliasProduct = aliasProduct;
            _kindOfReport = kindOfReport;
            _PathImmagine = PathImmagine;
            _isAveraged = isAveraged;
            _listaAnni = listaAnni;
            _listaPartnersImport = listaPartnersImport;
            _listaPartnersExport = listaPartnersExport;
        }

        public void Genera()
        {
            FileInfo newFile;
            newFile = CheckFile(_filePath);

            GestisciCaricamentoInizialeDati(_reporter, _aliasProduct);

            using (ExcelPackage package = new ExcelPackage(newFile))
            {
                //// Creiamo il Tab IMPORT
                GeneraSheetImport(package);

                // Creiamo il Tab EXPORT
                GeneraSheetExport(package);

                package.Save();
                this.localPackage = package;
            }
        }

        private FileInfo CheckFile(string filePath)
        {
            FileInfo newFile = new FileInfo(filePath);
            if (newFile.Exists)
            {
                newFile.Delete();  // ensures we create a new workbook
                newFile = new FileInfo(filePath);
            }
            return newFile;
        }

        private void GestisciCaricamentoInizialeDati(string reporter, string aliasProdotto)
        {
            if (aliasProdotto == "Glass mineral wool")
            {
                var pic1 = from p in _db.RawData
                           where (p.REPORTER_LAB == reporter &&
                               p.PRODUCT > 70000000)
                           select p;

                var pic2 = from p in _db.RawData
                           where (p.PARTNER_LAB == reporter &&
                               p.PRODUCT > 70000000)
                           select p;

                QueryInizialeReporter = pic1.ToList();
                QueryInizialePartner = pic2.ToList();

                pic1 = null;
                pic2 = null;

            }
            else if (aliasProdotto == "Rock mineral wool")
            {
                var pic1 = from p in _db.RawData
                           where (p.REPORTER_LAB == reporter &&
                               p.PRODUCT == 68061000)
                           select p;

                var pic2 = from p in _db.RawData
                           where (p.PARTNER_LAB == reporter &&
                               p.PRODUCT == 68061000)
                           select p;

                QueryInizialeReporter = pic1.ToList();
                QueryInizialePartner = pic2.ToList();

                pic1 = null;
                pic2 = null;

            }
        }

        private ExcelRange CreaRangeBottomGrigetto(string Testo, int startingRow, int startingColumn, int endingRow, int endingColumn, ExcelWorksheet worksheet)
        {
            ExcelRange lrange10 = worksheet.Cells[startingRow, startingColumn, endingRow, endingColumn]; //starting row, starting column, ending row, ending column 
            lrange10.Merge = true;
            lrange10.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
            lrange10.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
            lrange10.Value = "Partners";
            lrange10.Style.Fill.PatternType = ExcelFillStyle.Solid;
            lrange10.Style.Fill.BackgroundColor.SetColor(cGrigetto);
            lrange10.Style.Font.Bold = true;
            lrange10.Style.Border.Bottom.Style = ExcelBorderStyle.Medium;
            lrange10.Style.Border.Top.Style = ExcelBorderStyle.Thin;
            lrange10.Style.Border.Left.Style = ExcelBorderStyle.Thin;
            lrange10.Style.Border.Right.Style = ExcelBorderStyle.Thin;

            return lrange10;
        }

        private int DammiIndiceColonna(int myIndex)
        {
            int IndiceColonna = 0;

            switch (myIndex)
            {
                case 0:
                    IndiceColonna = 2;
                    break;
                case 1:
                    IndiceColonna = 19;
                    break;
                case 2:
                    IndiceColonna = 36;
                    break;
                case 3:
                    IndiceColonna = 53;
                    break;
            }

            return IndiceColonna;
        }

        private void FillYearIntestazione(int rowStartIndex, int columnStartIndex, ExcelWorksheet worksheet, string _year)
        {

            ExcelRange lrange0 = worksheet.Cells[rowStartIndex - 1, columnStartIndex, rowStartIndex - 1, columnStartIndex + 16];
            lrange0.Merge = true;
            lrange0.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
            lrange0.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
            lrange0.Value = _year;
            lrange0.Style.Fill.PatternType = ExcelFillStyle.Solid;
            lrange0.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.White);
            lrange0.Style.Font.Bold = true;
            lrange0.Style.Numberformat.Format = "@";
            lrange0.Style.Border.Top.Style = ExcelBorderStyle.Thin;
            lrange0.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
            lrange0.Style.Border.Left.Style = ExcelBorderStyle.Thin;
            lrange0.Style.Border.Right.Style = ExcelBorderStyle.Thin;


            //            worksheet.Cells["C2:C5"].Style.Numberformat.Format = "#,##0";


            //       Riempiamo Q1 che parte

            ExcelRange lrange1 = RiempiQuater_Fase1(rowStartIndex, columnStartIndex, worksheet, "Quarter 1");
            ExcelRange lrange4 = RiempiQuater_Fase1(rowStartIndex, columnStartIndex + 4, worksheet, "Quarter 2");
            ExcelRange lrange7 = RiempiQuater_Fase1(rowStartIndex, columnStartIndex + 8, worksheet, "Quarter 3");
            ExcelRange lrange10 = RiempiQuater_Fase1(rowStartIndex, columnStartIndex + 12, worksheet, "Quarter 4");
            ExcelRange lrange13 = RiempiQuater_Fase4(rowStartIndex, columnStartIndex + 16, worksheet, "Full Year");




            ExcelRange lrange2 = RiempiQuater_Fase2(rowStartIndex, columnStartIndex, worksheet, "1");
            ExcelRange lrange5 = RiempiQuater_Fase2(rowStartIndex, columnStartIndex + 4, worksheet, "2");
            ExcelRange lrange8 = RiempiQuater_Fase2(rowStartIndex, columnStartIndex + 8, worksheet, "3");
            ExcelRange lrange11 = RiempiQuater_Fase2(rowStartIndex, columnStartIndex + 12, worksheet, "4");
            ExcelRange lrange14 = RiempiQuater_Fase5(rowStartIndex, columnStartIndex + 16, worksheet);


            ExcelRange lrange3 = RiempiQuater_Fase3(rowStartIndex, columnStartIndex, worksheet, "Q1");
            ExcelRange lrange6 = RiempiQuater_Fase3(rowStartIndex, columnStartIndex + 4, worksheet, "Q2");
            ExcelRange lrange9 = RiempiQuater_Fase3(rowStartIndex, columnStartIndex + 8, worksheet, "Q3");
            ExcelRange lrange12 = RiempiQuater_Fase3(rowStartIndex, columnStartIndex + 12, worksheet, "Q4");
        }

        #region Dati

        private void RiempiDati(string Partner, ExcelWorksheet worksheet, int _year, string Flow, int InitialRow, int InitialColumn)
        {
            switch (_kindOfReport)
            {
                case "Ton":

                    RiempiDatiTon(Partner, worksheet, _year, Flow, InitialRow, InitialColumn);

                    break;
                case "Euro":

                    RiempiDatiEuro(Partner, worksheet, _year, Flow, InitialRow, InitialColumn);

                    break;

            }
        }

        private List<localSpostamentoDati> SubEstraiDati(string Partner, string Flow, int _year)
        {
            var ReporterList = from p in QueryInizialeReporter
                               where
                                   (p.PARTNER_LAB == Partner && p.FLOW_LAB == Flow)
                               orderby p.PERIOD
                               select p;


            string FlowB;

            if (Flow == "IMPORT")
            {
                FlowB = "EXPORT";
            }
            else
            {
                FlowB = "IMPORT";
            }

            var PartnerList = from p in QueryInizialePartner
                              where
                                  (p.REPORTER_LAB == Partner && p.FLOW_LAB == FlowB)
                              orderby p.PERIOD
                              select p;

            LibreriaNuovaExcel.collegamentoDb gdb = new LibreriaNuovaExcel.collegamentoDb(_reporter, Partner, Flow, _year, _isAveraged, ReporterList.ToList(), PartnerList.ToList());

            List<localSpostamentoDati> nDati = new List<localSpostamentoDati>();
            nDati = gdb.ListaDati;

            return nDati;


        }

        string ConvertiDatiNumero(int inMese, string _year)
        {

            switch (inMese)
            {
                case 1:

                    return "Jan. " + _year;

                case 2:

                    return "Feb. " + _year;
                case 3:

                    return "Mar. " + _year;
                case 4:

                    return "Apr. " + _year;
                case 5:

                    return "May. " + _year;
                case 6:

                    return "Jun. " + _year;
                case 7:

                    return "Jul. " + _year;
                case 8:

                    return "Aug. " + _year;
                case 9:

                    return "Sep. " + _year;
                case 10:

                    return "Oct. " + _year;
                case 11:

                    return "Nov. " + _year;
                case 12:

                    return "Dec. " + _year;
            }

            return "0";

        }

        private void RiempiDatiTon(string Partner, ExcelWorksheet worksheet, int _year, string Flow,  int InitialRow, int InitialColumn)
        {

            List<localSpostamentoDati> nDati = new List<localSpostamentoDati>();
            nDati = SubEstraiDati(Partner, Flow, _year);


            Decimal SommaTrimestre = 0;
            Decimal SommaAnno = 0;
            for (int i = 1; i <= 3; i++)
            {
                worksheet.Cells[InitialRow, InitialColumn + i - 1].Value = nDati[i - 1].ValoreTonnellata;
                SommaTrimestre += nDati[i - 1].ValoreTonnellata;
            }

            worksheet.Cells[InitialRow, InitialColumn + 3].Value = SommaTrimestre;
            SommaAnno += SommaTrimestre;
            SommaTrimestre = 0;

            ExcelRange lrange3 = worksheet.Cells[InitialRow, InitialColumn + 3, InitialRow, InitialColumn + 3];
            //            lrange3.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
            //            lrange3.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
            lrange3.Style.Fill.PatternType = ExcelFillStyle.Solid;
            lrange3.Style.Fill.BackgroundColor.SetColor(cGrigetto);
            lrange3.Style.Border.Top.Style = ExcelBorderStyle.Medium;
            lrange3.Style.Border.Bottom.Style = ExcelBorderStyle.Medium;
            lrange3.Style.Border.Left.Style = ExcelBorderStyle.Medium;
            lrange3.Style.Border.Right.Style = ExcelBorderStyle.Medium;

            for (int i = 4; i <= 6; i++)
            {
                worksheet.Cells[InitialRow, InitialColumn + i - 1 + 1].Value = nDati[i - 1].ValoreTonnellata;
                SommaTrimestre += nDati[i - 1].ValoreTonnellata;
            }

            worksheet.Cells[InitialRow, InitialColumn + 7].Value = SommaTrimestre;
            SommaAnno += SommaTrimestre;
            SommaTrimestre = 0;

            ExcelRange lrange4 = worksheet.Cells[InitialRow, InitialColumn + 7, InitialRow, InitialColumn + 7];
            //lrange4.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
            //lrange4.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
            lrange4.Style.Fill.PatternType = ExcelFillStyle.Solid;
            lrange4.Style.Fill.BackgroundColor.SetColor(cGrigetto);
            lrange4.Style.Border.Top.Style = ExcelBorderStyle.Medium;
            lrange4.Style.Border.Bottom.Style = ExcelBorderStyle.Medium;
            lrange4.Style.Border.Left.Style = ExcelBorderStyle.Medium;
            lrange4.Style.Border.Right.Style = ExcelBorderStyle.Medium;

            for (int i = 7; i <= 9; i++)
            {
                worksheet.Cells[InitialRow, InitialColumn + i - 1 + 2].Value = nDati[i - 1].ValoreTonnellata;
                SommaTrimestre += nDati[i - 1].ValoreTonnellata;
            }

            worksheet.Cells[InitialRow, InitialColumn + 11].Value = SommaTrimestre;
            SommaAnno += SommaTrimestre;
            SommaTrimestre = 0;
            ExcelRange lrange5 = worksheet.Cells[InitialRow, InitialColumn + 11, InitialRow, InitialColumn + 11];
            //lrange5.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
            //lrange5.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
            lrange5.Style.Fill.PatternType = ExcelFillStyle.Solid;
            lrange5.Style.Fill.BackgroundColor.SetColor(cGrigetto);
            lrange5.Style.Border.Top.Style = ExcelBorderStyle.Medium;
            lrange5.Style.Border.Bottom.Style = ExcelBorderStyle.Medium;
            lrange5.Style.Border.Left.Style = ExcelBorderStyle.Medium;
            lrange5.Style.Border.Right.Style = ExcelBorderStyle.Medium;

            for (int i = 10; i <= 12; i++)
            {
                worksheet.Cells[InitialRow, InitialColumn + i - 1 + 3].Value = nDati[i - 1].ValoreTonnellata;
                SommaTrimestre += nDati[i - 1].ValoreTonnellata;
            }


            worksheet.Cells[InitialRow, InitialColumn + 15].Value = SommaTrimestre;
            SommaAnno += SommaTrimestre;
            worksheet.Cells[InitialRow, InitialColumn + 16].Value = SommaAnno;
            ExcelRange lrange7 = worksheet.Cells[InitialRow, InitialColumn + 15, InitialRow, InitialColumn + 16];
            //lrange7.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
            //lrange7.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
            lrange7.Style.Fill.PatternType = ExcelFillStyle.Solid;
            lrange7.Style.Fill.BackgroundColor.SetColor(cGrigetto);
            lrange7.Style.Border.Top.Style = ExcelBorderStyle.Medium;
            lrange7.Style.Border.Bottom.Style = ExcelBorderStyle.Medium;
            lrange7.Style.Border.Left.Style = ExcelBorderStyle.Medium;
            lrange7.Style.Border.Right.Style = ExcelBorderStyle.Medium;
        }

        private void RiempiDatiEuro(string Partner, ExcelWorksheet worksheet, int _year, string Flow, int InitialRow, int InitialColumn)
        {
            List<localSpostamentoDati> nDati = new List<localSpostamentoDati>();
            nDati = SubEstraiDati(Partner, Flow, _year);


            Decimal SommaTrimestre = 0;
            Decimal SommaAnno = 0;
            for (int i = 1; i <= 3; i++)
            {
                string period_lab = ConvertiDatiNumero(i, _year.ToString());

                worksheet.Cells[InitialRow, InitialColumn + i - 1].Value = nDati[i - 1].ValoreEuro;
                worksheet.Cells[InitialRow, InitialColumn + i - 1].Style.Numberformat.Format = @"#,##0.00\ [$€-1]";
                SommaTrimestre += nDati[i - 1].ValoreEuro;
            }

            worksheet.Cells[InitialRow, InitialColumn + 3].Value = SommaTrimestre;
            worksheet.Cells[InitialRow, InitialColumn + 3].Style.Numberformat.Format = @"#,##0.00\ [$€-1]";
            SommaAnno += SommaTrimestre;
            SommaTrimestre = 0;

            ExcelRange lrange3 = worksheet.Cells[InitialRow, InitialColumn + 3, InitialRow, InitialColumn + 3];
            lrange3.Style.Fill.PatternType = ExcelFillStyle.Solid;
            lrange3.Style.Fill.BackgroundColor.SetColor(cGrigetto);
            lrange3.Style.Border.Top.Style = ExcelBorderStyle.Medium;
            lrange3.Style.Border.Bottom.Style = ExcelBorderStyle.Medium;
            lrange3.Style.Border.Left.Style = ExcelBorderStyle.Medium;
            lrange3.Style.Border.Right.Style = ExcelBorderStyle.Medium;

            for (int i = 4; i <= 6; i++)
            {
                string period_lab = ConvertiDatiNumero(i, _year.ToString());

                worksheet.Cells[InitialRow, InitialColumn + i - 1 + 1].Value = nDati[i - 1].ValoreEuro;
                worksheet.Cells[InitialRow, InitialColumn + i - 1 + 1].Style.Numberformat.Format = @"#,##0.00\ [$€-1]";

                SommaTrimestre += nDati[i - 1].ValoreEuro;
            }

            worksheet.Cells[InitialRow, InitialColumn + 7].Value = SommaTrimestre;
            worksheet.Cells[InitialRow, InitialColumn + 7].Style.Numberformat.Format = @"#,##0.00\ [$€-1]";

            SommaAnno += SommaTrimestre;
            SommaTrimestre = 0;

            ExcelRange lrange4 = worksheet.Cells[InitialRow, InitialColumn + 7, InitialRow, InitialColumn + 7];
            lrange4.Style.Fill.PatternType = ExcelFillStyle.Solid;
            lrange4.Style.Fill.BackgroundColor.SetColor(cGrigetto);
            lrange4.Style.Border.Top.Style = ExcelBorderStyle.Medium;
            lrange4.Style.Border.Bottom.Style = ExcelBorderStyle.Medium;
            lrange4.Style.Border.Left.Style = ExcelBorderStyle.Medium;
            lrange4.Style.Border.Right.Style = ExcelBorderStyle.Medium;

            for (int i = 7; i <= 9; i++)
            {
                string period_lab = ConvertiDatiNumero(i, _year.ToString());


                worksheet.Cells[InitialRow, InitialColumn + i - 1 + 2].Value = nDati[i - 1].ValoreEuro;
                worksheet.Cells[InitialRow, InitialColumn + i - 1 + 2].Style.Numberformat.Format = @"#,##0.00\ [$€-1]";

                SommaTrimestre += nDati[i - 1].ValoreEuro;
            }

            worksheet.Cells[InitialRow, InitialColumn + 11].Value = SommaTrimestre;
            worksheet.Cells[InitialRow, InitialColumn + 11].Style.Numberformat.Format = @"#,##0.00\ [$€-1]";

            SommaAnno += SommaTrimestre;
            SommaTrimestre = 0;

            ExcelRange lrange5 = worksheet.Cells[InitialRow, InitialColumn + 11, InitialRow, InitialColumn + 11];
            lrange5.Style.Fill.PatternType = ExcelFillStyle.Solid;
            lrange5.Style.Fill.BackgroundColor.SetColor(cGrigetto);
            lrange5.Style.Border.Top.Style = ExcelBorderStyle.Medium;
            lrange5.Style.Border.Bottom.Style = ExcelBorderStyle.Medium;
            lrange5.Style.Border.Left.Style = ExcelBorderStyle.Medium;
            lrange5.Style.Border.Right.Style = ExcelBorderStyle.Medium;

            for (int i = 10; i <= 12; i++)
            {


                string period_lab = ConvertiDatiNumero(i, _year.ToString());

                worksheet.Cells[InitialRow, InitialColumn + i - 1 + 3].Value = nDati[i - 1].ValoreEuro;
                worksheet.Cells[InitialRow, InitialColumn + i - 1 + 3].Style.Numberformat.Format = @"#,##0.00\ [$€-1]";

                SommaTrimestre += nDati[i - 1].ValoreEuro;
            }


            worksheet.Cells[InitialRow, InitialColumn + 15].Value = SommaTrimestre;
            worksheet.Cells[InitialRow, InitialColumn + 15].Style.Numberformat.Format = @"#,##0.00\ [$€-1]";

            SommaAnno += SommaTrimestre;
            worksheet.Cells[InitialRow, InitialColumn + 16].Value = SommaAnno;
            worksheet.Cells[InitialRow, InitialColumn + 16].Style.Numberformat.Format = @"#,##0.00\ [$€-1]";

            ExcelRange lrange7 = worksheet.Cells[InitialRow, InitialColumn + 15, InitialRow, InitialColumn + 16];
            //lrange7.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
            //lrange7.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
            lrange7.Style.Fill.PatternType = ExcelFillStyle.Solid;
            lrange7.Style.Fill.BackgroundColor.SetColor(cGrigetto);
            lrange7.Style.Border.Top.Style = ExcelBorderStyle.Medium;
            lrange7.Style.Border.Bottom.Style = ExcelBorderStyle.Medium;
            lrange7.Style.Border.Left.Style = ExcelBorderStyle.Medium;
            lrange7.Style.Border.Right.Style = ExcelBorderStyle.Medium;
        }

        private string ConvertiNumeroinLettera(int IndiceColonna)
        {

            switch (IndiceColonna)
            {
                case 2:

                    return "B";

                case 3:

                    return "C";
                case 4:

                    return "D";
                case 5:

                    return "E";
                case 6:

                    return "F";
                case 7:

                    return "G";
                case 8:

                    return "H";
                case 9:

                    return "I";
                case 10:

                    return "J";
                case 11:

                    return "K";
                case 12:

                    return "L";
                case 13:

                    return "M";
                case 14:

                    return "N";
                case 15:

                    return "O";
                case 16:

                    return "P";
                case 17:

                    return "Q";
                case 18:


                    return "R";
                case 19:

                    return "S";
                case 20:

                    return "T";
                case 21:

                    return "U";
                case 22:

                    return "V";
                case 23:

                    return "W";
                case 24:

                    return "X";
                case 25:

                    return "Y";
                case 26:

                    return "Z";
                case 27:

                    return "AA";
                case 28:

                    return "AB";

                case 29:

                    return "AC";
                case 30:

                    return "AD";
                case 31:

                    return "AE";
                case 32:

                    return "AF";
                case 33:

                    return "AG";
                case 34:

                    return "AH";
                case 35:

                    return "AI";
                case 36:

                    return "AJ";
                case 37:

                    return "AK";
                case 38:

                    return "AL";
                case 39:

                    return "AM";
                case 40:

                    return "AN";
                case 41:

                    return "AO";
                case 42:

                    return "AP";
                case 43:

                    return "AQ";
                case 44:

                    return "AR";
                case 45:

                    return "AS";
                case 46:

                    return "AT";
                case 47:

                    return "AU";
                case 48:

                    return "AV";
                case 49:

                    return "AW";
                case 50:

                    return "AX";
                case 51:

                    return "AY";
                case 52:

                    return "AZ";
                case 53:

                    return "BA";
                case 54:

                    return "BB";

                case 55:

                    return "BC";
                case 56:

                    return "BD";
                case 57:

                    return "BE";
                case 58:

                    return "BF";
                case 59:

                    return "BG";
                case 60:

                    return "BH";
                case 61:

                    return "BI";
                case 62:

                    return "BJ";
                case 63:

                    return "BK";
                case 64:

                    return "BL";
                case 65:

                    return "BM";
                case 66:

                    return "BN";
                case 67:

                    return "BO";
                case 68:

                    return "BP";

                case 69:
                    return "BQ";

                case 70:

                    return "BR";
                case 71:

                    return "BS";
                case 72:

                    return "BT";
                case 73:

                    return "BU";
                case 74:

                    return "BV";
                case 75:

                    return "BW";
                case 76:

                    return "BX";
                case 77:

                    return "BY";
                case 78:

                    return "BZ";
            }

            return "0";


        }

        #endregion




        private void GeneraSheetExport(ExcelPackage package)
        {
            _GeneraSheet(package, "Export", _listaPartnersExport);
        }

        private void GeneraSheetImport(ExcelPackage package)
        {
            _GeneraSheet(package, "Import", _listaPartnersImport);
        }

        private void _GeneraSheet(ExcelPackage package, string Flusso, List<string> _listaPartners)
        {
            // add a new worksheet to the empty workbook
            ExcelWorksheet worksheet = package.Workbook.Worksheets.Add(Flusso);

            FileInfo newpic = new FileInfo(_PathImmagine);
            ExcelPicture pic = worksheet.Drawings.AddPicture("Bitmap1", newpic);
            pic.From.Column = 0;
            pic.To.Column = 1;
            pic.From.Row = 0;
            pic.To.Row = 3;


            //Add the headers
            worksheet.Cells[6, 1].Value = "Country";
            worksheet.Cells[7, 1].Value = "Product";
            worksheet.Cells[8, 1].Value = "Flow";
            worksheet.Cells[9, 1].Value = "Value";

            worksheet.Cells[6, 2].Value = _reporter;
            worksheet.Cells[7, 2].Value = _aliasProduct;
            worksheet.Cells[8, 2].Value = Flusso.ToUpper();
            worksheet.Cells[9, 2].Value = _kindOfReport;

            worksheet.Column(1).Width = 35.5;
            worksheet.Column(2).Width = 18.5;

            for (int ii = 3; ii <= 75; ii++)
            {
                worksheet.Column(ii).Width = 14.5;
            }

            using (var range = worksheet.Cells[6, 1, 9, 1])
            {
                range.Style.Font.Bold = true;
                range.Style.Fill.PatternType = ExcelFillStyle.Solid;
                range.Style.Fill.BackgroundColor.SetColor(cMarrone);
            }

            using (var range = worksheet.Cells[6, 2, 6, 2])
            {
                range.Style.Fill.PatternType = ExcelFillStyle.Solid;
                range.Style.Fill.BackgroundColor.SetColor(cAzzurrino);
            }

            using (var range = worksheet.Cells[8, 2, 8, 2])
            {
                range.Style.Fill.PatternType = ExcelFillStyle.Solid;
                range.Style.Fill.BackgroundColor.SetColor(cAzzurrino);
            }


            ExcelRange RangePartners;
            RangePartners = CreaRangeBottomGrigetto("Partners", 12, 1, 13, 1, worksheet);


            int localIndice = 0;
            int IndiceColonna = 0;
            int rowIndex = 13;
            int rowIndexInitial = 13;


            foreach (int tmpAnno in _listaAnni)
            {
                IndiceColonna = DammiIndiceColonna(localIndice);
                FillYearIntestazione(12, IndiceColonna, worksheet, tmpAnno.ToString());
                localIndice += 1;
            }



            foreach (string tmpPartner in _listaPartners)
            {
                worksheet.Cells[rowIndex + 1, 1].Value = tmpPartner;
                worksheet.Cells[rowIndex + 1, 1].Style.Border.Right.Style = ExcelBorderStyle.Medium;

                int IndiceSecondaColonna = 0;
                foreach (int tmpAnno in _listaAnni)
                {
                    RiempiDati( tmpPartner, worksheet, tmpAnno, Flusso.ToUpper(), rowIndex + 1, 2 + IndiceSecondaColonna * 17);

                    IndiceSecondaColonna += 1;
                }

                rowIndex += 1;
            }

            worksheet.Cells[rowIndex + 1, 1].Value = "Totals:";
            worksheet.Cells[rowIndex + 1, 1].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
            worksheet.Cells[rowIndex + 1, 1].Style.Border.Top.Style = ExcelBorderStyle.Thin;
            worksheet.Cells[rowIndex + 1, 1].Style.Border.Left.Style = ExcelBorderStyle.Thin;
            worksheet.Cells[rowIndex + 1, 1].Style.Border.Right.Style = ExcelBorderStyle.Medium;


            for (int m = 1; m <= _listaAnni.Count; m++)
            {
                int Minimo = 0;
                int Massimo = 0;
                switch (m)
                {
                    case 1:

                        Minimo = 2;
                        Massimo = 18;
                        break;

                    case 2:

                        Minimo = 19;
                        Massimo = 35;
                        break;

                    case 3:

                        Minimo = 37;
                        Massimo = 52;
                        break;

                    case 4:

                        Minimo = 55;
                        Massimo = 69;
                        break;


                }

                List<string> MyList = new List<string>();
                for (int j = Minimo; j <= Massimo; j++)
                {
                    string Indicione = ConvertiNumeroinLettera(j);
                    string SommaUno = "SUM(" + Indicione + (rowIndexInitial + 1).ToString() + ":" + Indicione + (rowIndex).ToString() + ")";
                    MyList.Add(SommaUno);
                    worksheet.Cells[rowIndex + 1, j].Formula = MyList[MyList.Count - 1];


                    if (_kindOfReport == "Euro")
                    {
                        worksheet.Cells[rowIndex + 1, j].Style.Numberformat.Format = @"#,##0.00\ [$€-1]";
                    }

                    using (var range = worksheet.Cells[rowIndex + 1, j, rowIndex + 1, j])
                    {
                        range.Style.Font.Bold = true;
                        range.Style.Fill.PatternType = ExcelFillStyle.Solid;
                        range.Style.Fill.BackgroundColor.SetColor(cMarrone);
                    }

                }

            }


        }

        private ExcelRange RiempiQuater_Fase1(int rowStartIndex, int columnStartIndex, ExcelWorksheet worksheet, string Testo)
        {
            ExcelRange lrange2 = worksheet.Cells[rowStartIndex, columnStartIndex, rowStartIndex, columnStartIndex + 2]; //starting row, starting column, ending row, ending column 
            lrange2.Merge = true;
            lrange2.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
            lrange2.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
            lrange2.Value = Testo;
            lrange2.Style.Fill.PatternType = ExcelFillStyle.Solid;
            lrange2.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.White);
            lrange2.Style.Font.Bold = true;
            lrange2.Style.Border.Top.Style = ExcelBorderStyle.Thin;
            lrange2.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
            lrange2.Style.Border.Left.Style = ExcelBorderStyle.Thin;
            lrange2.Style.Border.Right.Style = ExcelBorderStyle.Thin;


            return lrange2;
        }

        private ExcelRange RiempiQuater_Fase2(int rowStartIndex, int columnStartIndex, ExcelWorksheet worksheet, string Testo)
        {
            switch (Testo)
            {
                case "1":
                    worksheet.Cells[rowStartIndex + 1, columnStartIndex].Value = "Jan";
                    worksheet.Cells[rowStartIndex + 1, columnStartIndex + 1].Value = "Feb";
                    worksheet.Cells[rowStartIndex + 1, columnStartIndex + 2].Value = "Mar";

                    break;
                case "2":
                    worksheet.Cells[rowStartIndex + 1, columnStartIndex].Value = "Apr";
                    worksheet.Cells[rowStartIndex + 1, columnStartIndex + 1].Value = "May";
                    worksheet.Cells[rowStartIndex + 1, columnStartIndex + 2].Value = "Jun";

                    break;
                case "3":
                    worksheet.Cells[rowStartIndex + 1, columnStartIndex].Value = "Jul";
                    worksheet.Cells[rowStartIndex + 1, columnStartIndex + 1].Value = "Aug";
                    worksheet.Cells[rowStartIndex + 1, columnStartIndex + 2].Value = "Sep";

                    break;
                case "4":
                    worksheet.Cells[rowStartIndex + 1, columnStartIndex].Value = "Oct";
                    worksheet.Cells[rowStartIndex + 1, columnStartIndex + 1].Value = "Nov";
                    worksheet.Cells[rowStartIndex + 1, columnStartIndex + 2].Value = "Dec";

                    break;
            }

            ExcelRange lrange3 = worksheet.Cells[rowStartIndex + 1, columnStartIndex, rowStartIndex + 1, columnStartIndex + 2];
            lrange3.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
            lrange3.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
            lrange3.Style.Fill.PatternType = ExcelFillStyle.Solid;
            lrange3.Style.Fill.BackgroundColor.SetColor(cGrigetto);
            lrange3.Style.Font.Bold = true;
            lrange3.Style.Border.Top.Style = ExcelBorderStyle.Thin;
            lrange3.Style.Border.Bottom.Style = ExcelBorderStyle.Medium;
            lrange3.Style.Border.Left.Style = ExcelBorderStyle.Thin;
            lrange3.Style.Border.Right.Style = ExcelBorderStyle.Thin;


            return lrange3;

        }

        private ExcelRange RiempiQuater_Fase3(int rowStartIndex, int columnStartIndex, ExcelWorksheet worksheet, string Testo)
        {
            ExcelRange lrange4 = worksheet.Cells[rowStartIndex, columnStartIndex + 3, rowStartIndex + 1, columnStartIndex + 3]; //starting row, starting column, ending row, ending column 
            lrange4.Merge = true;
            lrange4.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
            lrange4.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
            lrange4.Value = Testo;
            lrange4.Style.Fill.PatternType = ExcelFillStyle.Solid;
            lrange4.Style.Fill.BackgroundColor.SetColor(cGrigetto);
            lrange4.Style.Font.Bold = true;
            lrange4.Style.Border.Top.Style = ExcelBorderStyle.Thin;
            lrange4.Style.Border.Bottom.Style = ExcelBorderStyle.Medium;
            lrange4.Style.Border.Left.Style = ExcelBorderStyle.Thin;
            lrange4.Style.Border.Right.Style = ExcelBorderStyle.Thin;



            return lrange4;
        }

        private ExcelRange RiempiQuater_Fase4(int rowStartIndex, int columnStartIndex, ExcelWorksheet worksheet, string Testo)
        {
            ExcelRange lrange2 = worksheet.Cells[rowStartIndex, columnStartIndex, rowStartIndex, columnStartIndex]; //starting row, starting column, ending row, ending column 
            lrange2.Merge = true;
            lrange2.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
            lrange2.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
            lrange2.Value = Testo;
            lrange2.Style.Fill.PatternType = ExcelFillStyle.Solid;
            lrange2.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.White);
            lrange2.Style.Font.Bold = true;
            lrange2.Style.Border.Top.Style = ExcelBorderStyle.Thin;
            lrange2.Style.Border.Bottom.Style = ExcelBorderStyle.Medium;
            lrange2.Style.Border.Left.Style = ExcelBorderStyle.Thin;
            lrange2.Style.Border.Right.Style = ExcelBorderStyle.Thin;

            return lrange2;
        }

        private ExcelRange RiempiQuater_Fase5(int rowStartIndex, int columnStartIndex, ExcelWorksheet worksheet)
        {
            worksheet.Cells[rowStartIndex + 1, columnStartIndex].Value = "Full Year";

            ExcelRange lrange3 = worksheet.Cells[rowStartIndex + 1, columnStartIndex, rowStartIndex + 1, columnStartIndex];
            lrange3.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
            lrange3.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
            lrange3.Style.Fill.PatternType = ExcelFillStyle.Solid;
            lrange3.Style.Fill.BackgroundColor.SetColor(cGrigetto);
            lrange3.Style.Font.Bold = true;
            lrange3.Style.Border.Top.Style = ExcelBorderStyle.Thin;
            lrange3.Style.Border.Bottom.Style = ExcelBorderStyle.Medium;
            lrange3.Style.Border.Left.Style = ExcelBorderStyle.Thin;
            lrange3.Style.Border.Right.Style = ExcelBorderStyle.Thin;

            return lrange3;
        }

    }
}
